

/**
	Author		: Rohan
	Date		: May 19th 2004
	Description	: Removes the specified holds on the given sales order. The value 0 in for a hold 
                          indicates that the hold has to set and 1 indicates the hold to be removed.
			  It will also update the COOL_Status and Sales Order Status in the COOL table and
			  sales order tables respectively
	Usage		: exec sp_ct_remove_sales_order_holds 2444, 1, 1, 1, 1
	
	select * from sales_hold_table where lineitem_id in ( select lineitem_id from customer_open_order_lineitem where salesorder_id = 2444 )		
	UPDATE SALES_HOLD_TABLE SET HOLD_REASON_CODE = '0', 
			    IS_APPROVED      = '1', 	
			    HOLD_BY_USER     = 'SYSTEM', 
                            TIMESTAMP        = CURRENT_TIMESTAMP WHERE LINEITEM_ID IN ( SELECT LINEITEM_ID FROM CUSTOMER_OPEN_ORDER_LINEITEM NOLOCK WHERE SALESORDER_ID = 2444 AND COOL_STATUS <> 'SHIPPED' AND LINEITEM_ID NOT IN ( SELECT LINEITEM_ID FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE NOLOCK WHERE  CSLIT_STATUS = 'SHIPPED' OR CSLIT_STATUS = 'ISSUED' ) )

	
*/
ALTER PROC sp_ct_remove_sales_order_holds

@SALES_ORDER_ID         INT,
@REMOVE_OPERATIONS_HOLD INT,
@REMOVE_MARKETTING_HOLD INT,
@REMOVE_SALES_HOLD      INT,
@REMOVE_FINANCE_HOLD    INT

AS
BEGIN

--DECLARATIONS
DECLARE @TRUE                  INT
DECLARE @FALSE                 INT
DECLARE @LINE_ITEM_ID          INT
DECLARE @IS_LINE_ITEM_APPROVED INT
DECLARE @COOL_STATUS           VARCHAR(20)

DECLARE @LINE_ITEMS_CURSOR CURSOR

--INITIALISATIONS
SET @TRUE  = 1
SET @FALSE = 0


--SETTING ALL THE HOLDS FOR ALL THE LINE ITEMS IN THIS SALES ORDER WHICH ARE NOT SHIPPED, PARTIALLY SHIPPED OR PS ISSUED
UPDATE SALES_HOLD_TABLE SET HOLD_REASON_CODE = '0', 
			    IS_APPROVED      = '1', 	
			    HOLD_BY_USER     = 'SYSTEM', 
                            TIMESTAMP        = CURRENT_TIMESTAMP WHERE LINEITEM_ID IN ( SELECT LINEITEM_ID FROM CUSTOMER_OPEN_ORDER_LINEITEM NOLOCK WHERE SALESORDER_ID = @SALES_ORDER_ID AND COOL_STATUS <> 'SHIPPED' AND LINEITEM_ID NOT IN ( SELECT LINEITEM_ID FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE NOLOCK WHERE  CSLIT_STATUS = 'SHIPPED' OR CSLIT_STATUS = 'ISSUED' ) )
	


IF( @REMOVE_OPERATIONS_HOLD = @TRUE )
BEGIN
	--REMOVING OPERATIONS HOLD ON ALL THE LINE ITEMS OF THIS SALES ORDER 
	UPDATE SALES_HOLD_TABLE SET IS_APPROVED = 0, Hold_Reason_Code = '' WHERE HOLD_BY_GROUP = 'OPERATIONS' AND SALESORDER_ID = @SALES_ORDER_ID
END

IF( @REMOVE_MARKETTING_HOLD = @TRUE )
BEGIN
	--REMOVING MARKETTING HOLD ON ALL THE LINE ITEMS OF THIS SALES ORDER 
	UPDATE SALES_HOLD_TABLE SET IS_APPROVED = 0, Hold_Reason_Code = '' WHERE HOLD_BY_GROUP = 'MARKETING' AND SALESORDER_ID = @SALES_ORDER_ID
END

IF( @REMOVE_SALES_HOLD = @TRUE )
BEGIN
	--REMOVING SALES HOLD ON ALL THE LINE ITEMS OF THIS SALES ORDER 
	UPDATE SALES_HOLD_TABLE SET IS_APPROVED = 0, Hold_Reason_Code = '' WHERE HOLD_BY_GROUP = 'SALES' AND SALESORDER_ID = @SALES_ORDER_ID
END

IF( @REMOVE_FINANCE_HOLD = @TRUE )
BEGIN
	--REMOVING FINANCE HOLD ON ALL THE LINE ITEMS OF THIS SALES ORDER 
	UPDATE SALES_HOLD_TABLE SET IS_APPROVED = 0, Hold_Reason_Code = '' WHERE HOLD_BY_GROUP = 'FINANCE' AND SALESORDER_ID = @SALES_ORDER_ID
END

--UPDATING THE LINE ITEM STATUS
SET @LINE_ITEMS_CURSOR = CURSOR FOR SELECT LINEITEM_ID FROM CUSTOMER_OPEN_ORDER_LINEITEM NOLOCK WHERE SALESORDER_ID = @SALES_ORDER_ID AND LINEITEM_ID NOT IN ( SELECT LINEITEM_ID FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE NOLOCK WHERE  CSLIT_STATUS = 'SHIPPED' OR CSLIT_STATUS = 'ISSUED' ) 
OPEN @LINE_ITEMS_CURSOR
FETCH NEXT FROM @LINE_ITEMS_CURSOR INTO @LINE_ITEM_ID

WHILE( @@FETCH_STATUS = 0 )
BEGIN
	--FETCHING THE STATUS OF THE LINE ITEM
	SELECT @COOL_STATUS = COOL_Status FROM Customer_Open_Order_LineItem NOLOCK WHERE LineItem_ID = @LINE_ITEM_ID
	
	--CHECKING IF THERE ARE ANY HOLDS ON THE LINE ITEM
	SELECT @LINE_ITEM_ID = LINEITEM_ID FROM Sales_Hold_Table WHERE LineItem_ID = @LINE_ITEM_ID and Is_Approved = 1							
	IF( @@ROWCOUNT = 0 )
	BEGIN
		--THERE ARE NO HOLDS ON THE LINE ITEM 
		IF( @COOL_STATUS <> 'SHIPPED' )
	      	BEGIN
			UPDATE Customer_Open_Order_LineItem SET COOL_Status = 'APPROVED',TimeStamp = CURRENT_TIMESTAMP	WHERE LineItem_ID = @LINE_ITEM_ID
      		END
	END
	ELSE
	BEGIN
		--AS THERE ARE HOLDS ON THE LINE ITEM CHANGE THE COOL STATUS TO HOLD
		UPDATE Customer_Open_Order_LineItem SET COOL_Status = 'HOLD',Timestamp = CURRENT_TIMESTAMP WHERE LineItem_ID = @LINE_ITEM_ID
	END
	
	FETCH NEXT FROM @LINE_ITEMS_CURSOR INTO @LINE_ITEM_ID
END

CLOSE      @LINE_ITEMS_CURSOR
DEALLOCATE @LINE_ITEMS_CURSOR


--UPDATING THE SALES ORDER STATUS

--CHECKING IF THERE ARE ANY HOLDS ON ANY OF THE LINE ITEMS OF THIS SALES ORDER
SELECT @SALES_ORDER_ID = SALESORDER_ID FROM SALES_HOLD_TABLE NOLOCK WHERE SALESORDER_ID = @SALES_ORDER_ID AND IS_APPROVED = 1
IF( @@ROWCOUNT = 0 )
BEGIN
	--THERE ARE NO HOLDS SO APPROVING THE SALES ORDER
	UPDATE Sales_Order SET SO_Is_Pending = 1, SO_Status = 'APPROVED', TimeStamp = CURRENT_TIMESTAMP WHERE SalesOrder_ID = @SALES_ORDER_ID
END
ELSE
BEGIN
	UPDATE Sales_Order SET SO_Is_Pending = 0, SO_Status = 'HOLD',TimeStamp = CURRENT_TIMESTAMP WHERE SalesOrder_ID = @SALES_ORDER_ID
END

END --END OF PROC